package com.matt.repository;

import com.matt.domain.Contract;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

/**
 * ************************
 * Created by Ron
 * from com.matt
 * on 2018/6/14 0014.
 * ************************
 */
public interface ContractRepository extends JpaRepository<Contract, String>, JpaSpecificationExecutor<Contract> {

    List<Contract> findByClienteleId(String clienteleId);

    List<Contract> findByClienteleIdIn(String[] clienteleIds);

    //2019-01-09删除
//    List<Contract> findByAreaIdAndName(String areaId, String contractTitle);

    List<Contract> findByClienteleIdAndStatus(String clienteleId, Integer status);

    List<Contract> findByAreaIdAndClienteleIdOrderByCreatedDesc(String areaId, String clienteleId);

    List<Contract> findByStatus(Integer status);

    @Query(value = "select * from contract c where c.deleted = 0  and c.payStatus = 1 " +
            " AND clienteleId= :cid " +
            " AND c.areaId = :aid " +
            " AND c.endDate >= :endDate AND c.beginDate <= :beginDate "
            ,nativeQuery = true)
    List<Contract> findValidContractsByAreaIdAndClienteleIdAndQueryDate(@Param("aid") String areaId,
                                                                        @Param("cid") String clientId,
                                                                        @Param("beginDate") Date beginDate,
                                                                        @Param("endDate") Date endDate);

    @Query(value = "select * from contract c where c.deleted = 0  and c.payStatus = 1 " +
            " AND c.areaId = :aid " +
            " AND c.endDate >= :endDate AND c.beginDate <= :beginDate "
            ,nativeQuery = true)
    List<Contract> findValidContractsByAreaIdAndQueryDate(@Param("aid") String areaId,
                                                          @Param("beginDate") Date beginDate,
                                                          @Param("endDate") Date endDate);



//    @Query("select sum(c.practicalAmount) as paidAmount from Contract c where  c.paidDate <= :paidAmount and c.payStatus =:payStatus")
//    Long sumPaidAmountByPaidDateBeforeAndPayStatus(Date paidDate, Integer payStatus);

//    List<Contract> findByPayStatusAndPaidDateBeforeAndAreaIdAndFromChannelNot(Integer payStatus, Date date, String areaId, Integer fromChannel);
}
